1. Technical Field
This invention generally relates to computer systems, and more specifically relates to database constraints.
2. Background Art
Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval.
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Database constraints are known in the art, and are used to limit data that may be entered into a database column. For example, a range constraint for a column that includes the age of people may have a range constraint that limits the entries to integer values from 1 to 99. If someone tries to enter a negative number, or zero, or 110, the database manager will detect that the data specified for the age column does not satisfy the limits specified in the range constraint for the age column, and will reject the attempt to enter data that is not within the specified range. In this manner, data may be limited to expected ranges.
Another known database constraint is a foreign key constraint, which limits allowable values for a column to data entered into the same column in a different table. This concept is powerful and useful when tables are related. Thus, a foreign key constraint may be specified that will not allow entry of a record into a table unless a corresponding record in a different table exists. The foreign key constraints are limited, however, in constraining a column based on allowable values in a different column that is of the same data type. A foreign key constraint assures the same data is entered into two columns of different database tables. As a result, the foreign key constraints are necessarily limited to specifying allowable values for a column based on a column of the same data type.
Table check constraints are also known in DB2 that allow specifying conditions across multiple columns for a record to be entered into the table. For example, a table check constraint could be defined for the table that assures that every employee who was hired prior to 1986 must make more than $40,000 per year. For example, such a table check constraint would only allow entry of data into the table if data in a HIREDATE column is less than 1986 and data in a SALARY column is less than $40,000. While table check constraints are useful, they are constraints on a table, not on a specific column within the table.
Known database constraints do not allow specifying allowable values for one column based on data in a second column when the second column has a different data type than the first column. Without a way for specifying allowable values in one column based on data in a second column that may be of a different type than data in the first column, the computer industry will continue to suffer from constraints that do not provide the desired flexibility.